#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <pthread.h>
#include <string.h>
#include "mysqlite3.h"
#include "myds.h"

static sqlite3* db = NULL;
pthread_mutex_t db_mutex;
static char errmsg[128]={0};

//初始化表
bool mysql_init(){
    pthread_mutex_init(&db_mutex, NULL);
    //
    if(SQLITE_OK != sqlite3_open("mydb.db", &db)){
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return false;
    }
    //
    char* errMsg = NULL;
    char* sql = "CREATE TABLE IF NOT EXISTS tbl_user ("
                "userid TEXT PRIMARY KEY NOT NULL,"
                "pwd TEXT NOT NULL);";
    if (sqlite3_exec(db, sql, 0, 0, &errMsg) != SQLITE_OK) {
        fprintf(stderr, "(1)SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
        return false;
    }
    
    sql = "CREATE TABLE IF NOT EXISTS tbl_dev ("
          "userid TEXT NOT NULL,"
          "devid TEXT NOT NULL,"
          "devname TEXT NOT NULL,"
          "desc TEXT NOT NULL,"
          "PRIMARY KEY (userid, devid));";
    if (sqlite3_exec(db, sql, 0, 0, &errMsg) != SQLITE_OK) {
        fprintf(stderr, "(2)SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
        return false;
    }
    return true;
}

void mysql_deinit(){
    if (db) {
        sqlite3_close(db);
        db = NULL;
    }
}

//获取sqlite3连接: 上锁！
sqlite3* mysql_getConn(){
    pthread_mutex_lock(&db_mutex);
    return db;
}

//解锁！
void mysql_closeConn(sqlite3* db){
    pthread_mutex_unlock(&db_mutex);
}

//user表：查询
bool mysql_user_exit(sqlite3* db, char* userid,char* pwd){
    char* sql = "select * from tbl_user where userid=? and pwd=?";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return false;
    }
    sqlite3_bind_text(stmt, 1, userid, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, pwd, -1, SQLITE_STATIC);
    int rc = sqlite3_step(stmt);
    if (rc == SQLITE_ROW) {
        // 用户存在
        sqlite3_finalize(stmt);
        return true;
    } else if (rc == SQLITE_DONE) {
        // 用户不存在
        sqlite3_finalize(stmt);
        return false;
    } else {
        fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        return false;
    }
    return true;
}

//user表：添加用户
bool mysql_user_add(sqlite3* db, my_user_t* user){
    char* sql = "INSERT INTO tbl_user (userid, pwd) VALUES (?, ?)";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return false;
    }
    
    sqlite3_bind_text(stmt, 1, user->userid, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, user->pwd, -1, SQLITE_STATIC);
    int rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        return false;
    }
    sqlite3_finalize(stmt);
    return true;
}

//dev表：查询userid 关联的 设备列表
bool mysql_dev_list(sqlite3* db, char* userid, my_dev_t* arr, int* len){
    printf("mysql_dev_list查询, user_id=[%s], strlen=[%ld]\n", userid, strlen(userid));
    char* sql = "SELECT * FROM tbl_dev WHERE userid=?";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return false;
    }
    
    sqlite3_bind_text(stmt, 1, userid, -1, SQLITE_STATIC);
    int rc = sqlite3_step(stmt);
    int count = 0;
    while (rc == SQLITE_ROW) {
        strcpy(arr[count].userid, (char*)sqlite3_column_text(stmt, 0));
        strcpy(arr[count].devid, (char*)sqlite3_column_text(stmt, 1));
        strcpy(arr[count].devname, (char*)sqlite3_column_text(stmt, 2));
        strcpy(arr[count].desc, (char*)sqlite3_column_text(stmt, 3));
        count++;
        rc = sqlite3_step(stmt);
    }
    *len = count;
    sqlite3_finalize(stmt);
    return true;
}

//dev表：添加关联的设备
bool mysql_dev_add(sqlite3* db, my_dev_t* dev){
    char* sql = "INSERT INTO tbl_dev (userid, devid, devname, desc) VALUES (?, ?, ?, ?)";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return false;
    }
    
    sqlite3_bind_text(stmt, 1, dev->userid, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, dev->devid, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 3, dev->devname, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 4, dev->desc, -1, SQLITE_STATIC);
    
    int rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        return false;
    }
    sqlite3_finalize(stmt);
    return true;
}

//dev表：删除关联的设备
bool mysql_dev_del(sqlite3* db, char* userid, char* devid){
    char* sql = "DELETE FROM tbl_dev WHERE userid=? AND devid=?";
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return false;
    }
    
    sqlite3_bind_text(stmt, 1, userid, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, devid, -1, SQLITE_STATIC);
    
    int rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        return false;
    }
    sqlite3_finalize(stmt);
    return true;
}